<?php  
/**
* 
*/
class Export_Model extends CI_Model
{
	
	function __construct()
	{
		parent::__construct();
		$this->amcamdb = $this->load->database('amcamdb', TRUE);
	}

	public function search_dataCDD_sequester($startdate = "",$enddate = ""){
		$condition = "";
		if($startdate != "" && $enddate != ""){
			$condition = "AND (convert(varchar,UVW_LAW_CDD_HR02.COMMAND_DATE,112)  >= convert(varchar,".$startdate.",112) 
				and convert(varchar,UVW_LAW_CDD_HR02.COMMAND_DATE,112)  <= convert(varchar,".$enddate.",112) )";
		}

		$sql = "SELECT DISTINCT UVW_LAW_CDD_HR02.CAC_HOLD_ASSET_COMMAND_OID + '_' + UVW_LAW_CDD_HR02.CEN_PERSON_OID AS HOLD_ID , UVW_LAW_CDD_HR02.CASE_DATA_ID,	
			(AMLOdb.dbo.TITLE.TITLE_FULL_TH+UVW_PETCASE_PERSON.CASE_NAME + ' '+ UVW_PETCASE_PERSON.CASE_LASTNAME + ' ' + UVW_PETCASE_PERSON.CASE_PHRASE) AS CASE_NAME,
			UVW_LAW_CDD_HR02.CAC_HOLD_ASSET_COMMAND_ID,UVW_LAW_CDD_HR02.COMMAND_DATE,UVW_LAW_CDD_HR02.RESOLUTION_TYPE,
			UVW_LAW_CDD_HR02.PET_CASE_DATA_OID,UVW_LAW_CDD_HR02.CARD_TYPE,UVW_LAW_CDD_HR02.CITIZEN_CARD_NUMBER,
			UVW_LAW_CDD_HR02.PREFIX,UVW_LAW_CDD_HR02.FIRST_NAME,UVW_LAW_CDD_HR02.LAST_NAME,UVW_LAW_CDD_HR02.BIRTH_DATE, 
			UVW_LAW_CDD_HR02.ADDRESS,UVW_LAW_CDD_HR02.POLITIC_DESC_TH,UVW_LAW_CDD_HR02.POLITIC_DESC_TH AS REGION_POLITIC_DESC_TH,
			UVW_LAW_CDD_HR02.TUMBON_ID,UVW_LAW_CDD_HR02.AMPHUR_ID,UVW_LAW_CDD_HR02.PROVINCE_ID,UVW_LAW_CDD_HR02.ZIP_CODE,
			UVW_LAW_CDD_HR02.ID_REMARK ,UVW_LAW_CDD_HR02.POLITIC_OID
		from UVW_LAW_CDD_HR02 	
			INNER JOIN UVW_PETCASE_PERSON ON UVW_LAW_CDD_HR02.CASE_DATA_ID = UVW_PETCASE_PERSON.PET_CASE_DATA_ID
			LEFT JOIN AMLOdb.dbo.TITLE ON UVW_PETCASE_PERSON.TITLE_OID = AMLOdb.dbo.TITLE.TITLE_OID
		where (1=1) " . $condition;
		//echo $sql;
		return $this->amcamdb->query($sql)->result_array();

	}

	public function search_dataCDD_assign($startdate = "",$enddate = ""){
		$condition = '';
		if ($startdate != "" && $enddate != "") {
					$condition = "AND (convert(varchar,UVW_LAW_CDD_HR03.COMMAND_DATE,112)  >= convert(varchar,".$startdate.",112)
						and convert(varchar,UVW_LAW_CDD_HR03.COMMAND_DATE,112)  <= convert(varchar,".$enddate.",112) )";
			}
		$sql = "SELECT DISTINCT UVW_LAW_CDD_HR03.CAC_ASSIGNMENT_COMMAND_OID + '_' + UVW_LAW_CDD_HR03.CEN_PERSON_OID AS HOLD_ID, UVW_LAW_CDD_HR03.CASE_DATA_ID ,			
			(AMLOdb.dbo.TITLE.TITLE_FULL_TH+UVW_PETCASE_PERSON.CASE_NAME + ' '+ UVW_PETCASE_PERSON.CASE_LASTNAME + ' ' + UVW_PETCASE_PERSON.CASE_PHRASE) as CASE_NAME,		
			UVW_LAW_CDD_HR03.CAC_ASSIGNMENT_COMMAND_OID as CAC_HOLD_ASSET_COMMAND_ID,UVW_LAW_CDD_HR03.COMMAND_DATE,UVW_LAW_CDD_HR03.RESOLUTION_TYPE,		
			UVW_LAW_CDD_HR03.PET_CASE_DATA_OID,UVW_LAW_CDD_HR03.CARD_TYPE,UVW_LAW_CDD_HR03.CITIZEN_CARD_NUMBER,UVW_LAW_CDD_HR03.ID_REMARK,		
			UVW_LAW_CDD_HR03.PREFIX,UVW_LAW_CDD_HR03.FIRST_NAME,UVW_LAW_CDD_HR03.LAST_NAME,UVW_LAW_CDD_HR03.BIRTH_DATE, 		
			UVW_LAW_CDD_HR03.ADDRESS,UVW_LAW_CDD_HR03.POLITIC_DESC_TH,UVW_LAW_CDD_HR03.POLITIC_DESC_TH as REGION_POLITIC_DESC_TH, 		
			UVW_LAW_CDD_HR03.TUMBON_ID,UVW_LAW_CDD_HR03.AMPHUR_ID,UVW_LAW_CDD_HR03.PROVINCE_ID,UVW_LAW_CDD_HR03.ZIP_CODE,		
			UVW_LAW_CDD_HR03.ID_REMARK , UVW_LAW_CDD_HR03.POLITIC_OID
		FROM UVW_LAW_CDD_HR03 			
			INNER JOIN UVW_PETCASE_PERSON ON UVW_LAW_CDD_HR03.CASE_DATA_ID = UVW_PETCASE_PERSON.PET_CASE_DATA_ID		
			LEFT JOIN AMLOdb.dbo.TITLE ON UVW_PETCASE_PERSON.TITLE_OID = AMLOdb.dbo.TITLE.TITLE_OID		
		WHERE (1=1) " . $condition;
		//echo $sql;
		return $this->amcamdb->query($sql)->result_array();
	}

}
?>